package com.example.event_resource_java.authority.controller.CommonController;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.example.event_resource_java.authority.server.ClassService;
import com.example.event_resource_java.commons.enums.EnumErrorCode;
import com.example.event_resource_java.commons.model.ImportStuClassBody;
import com.example.event_resource_java.commons.util.ApiResult;
import com.example.event_resource_java.commons.util.EhCacheUtil;
import com.example.event_resource_java.commons.util.JWTUtil;
import com.example.event_resource_java.commons.vo.ClassVO.StudentVO;
import com.example.event_resource_java.commons.vo.ErroeDownVo;
import com.example.event_resource_java.commons.vo.ExcelStudentVO;
import com.example.event_resource_java.commons.util.easyexcel.CustomerDailyImportListener;
import com.example.event_resource_java.commons.util.easyexcel.StringConverter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @ClassName ExcelController
 * @Description: excel的导入导出
 * @Author: ak
 * @CreateDate: 2024/4/17 10:17
 * @UpdateUser:
 * @UpdateDate: 2024/4/17 10:17
 * @UpdateRemark:
 * @Version: 1.0
 */
@Controller
@RequestMapping("/excels")
@CrossOrigin
public class ExcelController {

    @Resource
    private ClassService classService;

    /**
     * @auther: ak
     * @Description 学生信息批量导入
     * @param:
     * @return:
     * @date: 2024/4/17 10:35
     */
    @PostMapping("/import")
    @ResponseBody
    public ApiResult importExcel(MultipartFile file) throws Exception {

        // 判断文件格式
        if (!"application/vnd.ms-excel".equals(file.getContentType())) {
            return ApiResult.fail("文件格式错误，请选择.xls文件");
        }
        if (file == null) {
            return ApiResult.fail();
        }

        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(0);
        importParams.setHeadRows(1);

        List<ExcelStudentVO> excelStudentVOList = null;
        try {
            excelStudentVOList = ExcelImportUtil.importExcel(file.getInputStream(), ExcelStudentVO.class, importParams);
            file.getInputStream().close();
        } catch (NoSuchElementException e) {
            throw new RuntimeException("excel文件不能为空");
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
        // excel校验手机号 导入失败则生成错误excel
        StringBuffer errList = new StringBuffer();
        ArrayList<String> errStringList = new ArrayList<>();
        ArrayList<String> mobilList = new ArrayList<>();

        for (ExcelStudentVO excelStudentVO : excelStudentVOList) {
            if (ObjectUtil.isNull(excelStudentVO.getName()) || ObjectUtil.isNull(excelStudentVO.getSex())) {
                errStringList.add(errList.append("存在空数据").toString());
            }

            String mobile = excelStudentVO.getMobile();
            //手机号为空 格式不正确
            if (StrUtil.isEmpty(mobile) || !Pattern.matches("^1[3-9]\\d{9}$", mobile)) {
                errStringList.add(excelStudentVO.getName() + ": " + mobile + "手机号错误");
                continue;
            }
            // 正确但重复
            if (mobilList.contains(mobile)) {
                errStringList.add(excelStudentVO.getName() + ": " + mobile + "手机号重复");
            }else {
                mobilList.add(mobile);
            }
        }

        if (errStringList.size() != 0) {
            // 手机号有误 返回错误信息list
            //exportErrExcel(excelStudentVOList, errMobileList, duplicateMobilList, response);
            ApiResult<Object> fail = ApiResult.fail(EnumErrorCode.fail.getCode(), EnumErrorCode.excelimportErr.getMsg());
            if (errStringList.size() > 5) {
                fail.setData(JSONArray.parseArray(JSON.toJSONString(errStringList.subList(0, 4))));
            }else {
                fail.setData(JSONArray.parseArray(JSON.toJSONString(errStringList)));
            }
            return fail;
        }

        for (ExcelStudentVO excelStudentVO : excelStudentVOList) {
            if (excelStudentVO.getName() == null) {
                excelStudentVO.setName("");
            }
            if (excelStudentVO.getMobile() == null) {
                excelStudentVO.setMobile("");
            }
            if (excelStudentVO.getSex() == null) {
                excelStudentVO.setSex("");
            }
            String sex = excelStudentVO.getSex();
            if (sex.equals("男")) {
                excelStudentVO.setSex("1");
            }else if (sex.equals("女")) {
                excelStudentVO.setSex("2");
            }else if (sex.equals("未知")) {
                excelStudentVO.setSex("0");
            }
        }

        return ApiResult.success(excelStudentVOList);
    }

    /**
     * @auther: ak
     * @Description 学生信息批量导入
     * @param:
     * @return:
     * @date: 2024/4/17 10:35
     */
    @PostMapping("/importStuClass")
    @ResponseBody
    public ApiResult importExcelAfer(ImportStuClassBody importStuClassBody) throws Exception {
        MultipartFile file = importStuClassBody.getFile();

        // 判断文件格式
        if (!"application/vnd.ms-excel".equals(file.getContentType())) {
            return ApiResult.fail("文件格式错误，请选择.xls文件");
        }
        if (file == null) {
            return ApiResult.fail();
        }

        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(0);
        importParams.setHeadRows(1);

        List<ExcelStudentVO> excelStudentVOList = null;
        try {
            excelStudentVOList = ExcelImportUtil.importExcel(file.getInputStream(), ExcelStudentVO.class, importParams);
            file.getInputStream().close();
        } catch (NoSuchElementException e) {
            throw new RuntimeException("excel文件不能为空");
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
        // excel校验手机号 导入失败则生成错误信息
        StringBuffer errList = new StringBuffer();
        ArrayList<String> errStringList = new ArrayList<>();
        ArrayList<String> mobilList = new ArrayList<>();


        for (ExcelStudentVO excelStudentVO : excelStudentVOList) {
            String mobile = excelStudentVO.getMobile();
            //手机号为空 格式不正确
            if (StrUtil.isEmpty(mobile) || !Pattern.matches("^1[3-9]\\d{9}$", mobile)) {
                errStringList.add(excelStudentVO.getName() + ": " + mobile + "手机号错误");
                continue;
            }
            // 正确但重复
            if (mobilList.contains(mobile)) {
                errStringList.add(excelStudentVO.getName() + ": " + mobile + "手机号重复");
            }else {
                mobilList.add(mobile);
            }
        }

        if (errStringList.size() != 0) {
            // 手机号有误 返回错误信息list
            //exportErrExcel(excelStudentVOList, errMobileList, duplicateMobilList, response);
            ApiResult<Object> fail = ApiResult.fail(EnumErrorCode.fail.getCode(), EnumErrorCode.excelimportErr.getMsg());
            if (errStringList.size() > 5) {
                fail.setData(JSONArray.parseArray(JSON.toJSONString(errStringList.subList(0, 4))));
            }else {
                fail.setData(JSONArray.parseArray(JSON.toJSONString(errStringList)));
            }
            return fail;
        }

        for (ExcelStudentVO excelStudentVO : excelStudentVOList) {
            if (excelStudentVO.getName() == null) {
                excelStudentVO.setName("");
            }
            if (excelStudentVO.getMobile() == null) {
                excelStudentVO.setMobile("");
            }
            if (excelStudentVO.getSex() == null) {
                excelStudentVO.setSex("");
            }
            String sex = excelStudentVO.getSex();
            if (sex.equals("男")) {
                excelStudentVO.setSex("1");
            }else if (sex.equals("女")) {
                excelStudentVO.setSex("2");
            }else if (sex.equals("未知")) {
                excelStudentVO.setSex("0");
            }
        }

        ArrayList<StudentVO> studentVOList = new ArrayList<>();
        for (ExcelStudentVO excelStudentVO : excelStudentVOList) {
            StudentVO studentVO = new StudentVO();
            BeanUtils.copyProperties(excelStudentVO, studentVO);
        }
        // 添加班级学生
        return classService.batchSaveClass(importStuClassBody.getClassname(), studentVOList);
    }

    /**
     * @auther: ak
     * @Description 导出错误信息excel表格
     * @param:
     * @return:
     * @date: 2024/4/26 14:03
     */
    public void exportErrExcel(List<ExcelStudentVO> excelStudentVOList,
                               ArrayList<String> errMobileList,
                               ArrayList<String> duplicateMobilList,
                               HttpServletResponse response) throws IOException {
        String fileName = URLEncoder.encode("错误学生信息表", "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("学生错误信息表");
        Row r = sheet.createRow(0);
        r.createCell(0).setCellValue("姓名");
        r.createCell(1).setCellValue("手机号");
        r.createCell(2).setCellValue("性别");
        r.createCell(3).setCellValue("是否正确");
        int row = 1, col = 0;


        for (ExcelStudentVO excelStudentVO : excelStudentVOList) {
            String mobile = excelStudentVO.getMobile();
            XSSFRow sheetRow = sheet.createRow(row);
            sheetRow.createCell(col).setCellValue(excelStudentVO.getName());
            sheetRow.createCell(++col).setCellValue(excelStudentVO.getMobile());
            sheetRow.createCell(++col).setCellValue(excelStudentVO.getSex());

            if (errMobileList.contains(mobile)) {
                sheetRow.createCell(++col).setCellValue("手机号有误!");
            }else if (duplicateMobilList.contains(mobile)) {
                sheetRow.createCell(++col).setCellValue("手机号重复!");
            }
            row++;
            col = 0;
        }

    }

    /**
     * @auther: ak
     * @Description 导入模板下载
     * @param:
     * @return:
     * @date: 2024/4/26 13:42
     */
    @GetMapping("/export")
    public ApiResult exportExcel(HttpServletResponse response) throws IOException {
        String fileName = URLEncoder.encode("学生信息导入模板", "UTF-8");
        // 格式：姓名 用户名 性别
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("学生信息表");

        Row r = sheet.createRow(0);
        r.createCell(0).setCellValue("姓名");
        r.createCell(1).setCellValue("手机号");
        r.createCell(2).setCellValue("性别");

        ServletOutputStream ops = response.getOutputStream();

        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
        workbook.write(ops);
        ops.close();

        return ApiResult.success();
    }

    @PostMapping("/import2")
    @ResponseBody
    public ApiResult exportErrExcel2(MultipartFile file) throws IOException {
        if (!"application/vnd.ms-excel".equals(file.getContentType())) {
            return ApiResult.fail("文件格式错误，请选择.xls文件");
        }
        List<ExcelStudentVO> excelStudentVOList = new ArrayList<>();

        try {
            InputStream ips = file.getInputStream();
            excelStudentVOList = EasyExcel.read(ips)
                    .registerConverter(new StringConverter())
                    .registerReadListener(new CustomerDailyImportListener())
                    .head(ExcelStudentVO.class)
                    .sheet()
                    .headRowNumber(2)
                    .doReadSync();
        }catch (Exception e) {
            e.getMessage();
            //e.printStackTrace();
            return ApiResult.fail(e.getMessage());
        }
        if (excelStudentVOList.isEmpty()) {
            return ApiResult.fail("Excel数据为空，请检查");
        }
        return ApiResult.success();
    }


    /**
     * 验证格式是否正确、或者重复
     * @param excelStudentVOList
     * @return
     */
    private List<ExcelStudentVO> setStudent(List<ExcelStudentVO> excelStudentVOList){
        //先验null 集合非空
        List<ExcelStudentVO> exisNull = excelStudentVOList.stream()
                .filter(a-> Objects.isNull(a.getMobile())).collect(Collectors.toList());
        if(!exisNull.isEmpty()){
            return exisNull;
        }
        //获取格式不正确的手机号集合
        List<ExcelStudentVO> ex1 = excelStudentVOList.stream()
                .filter(a-> !Pattern.matches("^1[3-9]\\d{9}$", a.getMobile()))
                .collect(Collectors.toList());
        if(!ex1.isEmpty()){
            return ex1;
        }
        //获取重复数据
        //先分组
        Map<String, Long> nameCount = excelStudentVOList.stream()
                .collect(Collectors.groupingBy(ExcelStudentVO::getMobile, Collectors.counting()));

        //根据分组数据验证大于0的组别
        List<ExcelStudentVO> duplicatePeople = excelStudentVOList.stream()
                .filter(a -> nameCount.getOrDefault(a.getMobile(), 0L) > 1)
                .collect(Collectors.toList());
        if(!duplicatePeople.isEmpty()){//!Objects.nonNull(ex2)
            return duplicatePeople;
        }
        return excelStudentVOList;
    }

    /**
     * 导出错误信息
     * @param request
     * @param response
     * @throws Exception
     * @author kl
     */
    @GetMapping("/importError")
    @ResponseBody
    @Transactional
    public void download(HttpServletRequest request, HttpServletResponse response) throws Exception {
        // 告诉浏览器用什么软件可以打开此文件
        response.setHeader("content-Type", "application/vnd.ms-excel");
        // 下载文件的默认名称
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("学生导入错误信息", "UTF-8") + ".xls");
        //编码
        response.setCharacterEncoding("UTF-8");
        // 根据手机号去
        String jsonArray = EhCacheUtil.get("studentErrList" + JWTUtil.getUser().getMobile()).toString();
        JSONArray arrayList = JSONArray.parseArray(jsonArray);
        //转换为目标对象list
        List<ExcelStudentVO> groupList = JSONObject.parseArray(arrayList.toJSONString(), ExcelStudentVO.class);
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), ErroeDownVo.class, groupList);
        workbook.write(response.getOutputStream());
    }
}
